import pool from "@/lib/db";

const roleModule = {
  get: async (id) => {
    const sql = `SELECT r.*, p.id AS permission_id, p.name AS permission_name, p.path, p.sort, p.parent_id, p.created_at AS permission_created_at, p.updated_at AS permission_updated_at FROM roles r LEFT JOIN role_permission rp ON r.id = rp.role_id LEFT JOIN permissions p ON rp.permission_id = p.id WHERE r.id = ?`;
    const [rows] = await pool.query(sql, [id]);
    if (!rows.length) return null;
    if (rows[0].code === '0' || rows[0].code === 0) {
      const [allPermissions] = await pool.query('SELECT * FROM permissions');
      const existPermissionIds = new Set(rows.filter(r => r.permission_id).map(r => r.permission_id));
      const toInsert = allPermissions.filter(p => !existPermissionIds.has(p.id));
      if (toInsert.length > 0) {
        const values = toInsert.map(p => `(${id},${p.id})`).join(',');
        await pool.query(`INSERT IGNORE INTO role_permission (role_id, permission_id) VALUES ${values}`);
        const [newRows] = await pool.query(sql, [id]);
        rows.splice(0, rows.length, ...newRows);
      }
    }
    const role = {
      id: rows[0].id,
      name: rows[0].name,
      code: rows[0].code,
      description: rows[0].description,
      created_at: rows[0].created_at,
      updated_at: rows[0].updated_at,
      permissions: rows
        .filter(r => r.permission_id)
        .map(r => ({
          id: r.permission_id,
          name: r.permission_name,
          path: r.path,
          sort: r.sort,
          parent_id: r.parent_id,
          created_at: r.permission_created_at,
          updated_at: r.permission_updated_at,
        })),
    };
    return role;
  },
  getById: async (id) => {
    const ids = Array.isArray(id) ? id : [id];
    const sql = 'SELECT * FROM roles WHERE id IN (?)';
    const [rows] = await pool.query(sql, [ids]);
    return rows;
  },
  getByCode: async (code) => {
    const sql = 'SELECT * FROM roles WHERE code = ?';
    const [rows] = await pool.query(sql, [code]);
    return rows[0];
  },
  getAll: async () => {
    const sql = `SELECT r.*, p.id AS permission_id, p.name AS permission_name, p.path, p.sort, p.parent_id, p.created_at AS permission_created_at, p.updated_at AS permission_updated_at FROM roles r LEFT JOIN role_permission rp ON r.id = rp.role_id LEFT JOIN permissions p ON rp.permission_id = p.id ORDER BY r.id`;
    const [rows] = await pool.query(sql);
    const roleMap = new Map();
    for (const row of rows) {
      if (!roleMap.has(row.id)) {
        roleMap.set(row.id, {
          id: row.id,
          name: row.name,
          code: row.code,
          description: row.description,
          created_at: row.created_at,
          updated_at: row.updated_at,
          permissions: [],
        });
      }
      if (row.permission_id) {
        roleMap.get(row.id).permissions.push({
          id: row.permission_id,
          name: row.permission_name,
          path: row.path,
          sort: row.sort,
          parent_id: row.parent_id,
          created_at: row.permission_created_at,
          updated_at: row.permission_updated_at,
        });
      }
    }
    return Array.from(roleMap.values());
  },
  updateAll: async (id, name, description, code, permissions) => {
    try {
      const uploadRoleSql = `UPDATE roles SET name = ?, description = ?, code = ? WHERE id = ?`;
      const resUploadRole = await pool.query(uploadRoleSql, [name, description, code, id]);
      const deleteRolePermissionSql = `DELETE FROM role_permission WHERE role_id = ?`;
      const resDeleteRolePermission = await pool.query(deleteRolePermissionSql, [id]);
      const permissionsId = permissions.map(permission => permission.id);
      const insertRolePermissionSql = `INSERT INTO role_permission (role_id, permission_id) VALUES ?`;
      const values = permissionsId.map(permissionId => [id, permissionId]);
      const resInsertRolePermission = await pool.query(insertRolePermissionSql, [values]);
      return { resUploadRole, resDeleteRolePermission, resInsertRolePermission };
    } catch (error) {
      throw new Error('Failed to update role');
    }
  },
  deleteById: async (id) => {
    const ids = Array.isArray(id) ? id : [id];
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const placeholders = ids.map(() => '?').join(',');
      const checkSql = `SELECT COUNT(*) AS count FROM users WHERE role_id IN (${placeholders})`;
      const [checkResult] = await connection.query(checkSql, ids);
      if (checkResult[0].count > 0) { throw new Error('There are roles associated with users. Please unassociate them first'); }
      const deleteSql = `DELETE FROM roles WHERE id IN (${placeholders})`;
      const [result] = await connection.query(deleteSql, ids);
      await connection.commit();
      return { affectedRows: result.affectedRows, deletedIds: ids };
    } catch (error) {
      await connection.rollback();
      throw new Error('Failed to delete role');
    } finally {
      connection.release();
    }
  },
  insertIncludePermissions: async (name, description, code, permissions) => {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      const insertRoleSql = `INSERT INTO roles (name, description, code) VALUES (?, ?, ?)`;
      const [insertRoleResult] = await connection.query(insertRoleSql, [name, description, code]);
      const roleId = insertRoleResult.insertId;
      const insertRolePermissionSql = `INSERT INTO role_permission (role_id, permission_id) VALUES ?`;
      const values = permissions.map(permission => [roleId, permission.id]);
      const [insertRolePermissionResult] = await connection.query(insertRolePermissionSql, [values]);
      await connection.commit();
      return { roleId, insertRolePermissionResult };
    }
    catch (error) {
      await connection.rollback();
      throw new Error('Failed to insert role');
    } finally {
      connection.release();
    }
  }

}

export default roleModule